using AuthService.Domain.Entities;
using AuthService.Domain.Enums;
using AuthService.Domain.Repositories;
using AuthService.Infrastructure.DTOs;
using Dapper;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Logging;
using Npgsql;
using System.Data;
using HttpMethod = AuthService.Domain.Enums.HttpMethod;

namespace AuthService.Infrastructure.Repositories;

/// <summary>
/// 动态API端点仓储实现类
/// 使用Dapper进行数据访问，支持PostgreSQL数据库
/// </summary>
public class DynamicApiEndpointRepository : BaseRepository<DynamicApiEndpoint, Guid>, IDynamicApiEndpointRepository
{
    /// <summary>
    /// 构造函数
    /// </summary>
    /// <param name="configuration">配置</param>
    /// <param name="logger">日志记录器</param>
    public DynamicApiEndpointRepository(IConfiguration configuration, ILogger<DynamicApiEndpointRepository> logger)
        : base(configuration, logger)
    {
    }

    /// <summary>
    /// 获取表名
    /// </summary>
    protected override string GetTableName()
    {
        return "\"DynamicApiEndpoints\"";
    }

    /// <summary>
    /// 重写基类方法以处理DTO转换
    /// </summary>
    public override async Task<DynamicApiEndpoint?> GetByIdAsync(Guid id, CancellationToken cancellationToken = default)
    {
        const string sql = @"
            SELECT * FROM ""DynamicApiEndpoints""
            WHERE ""Id"" = @Id AND ""IsDeleted"" = false";

        try
        {
            using var connection = CreateConnection();
            var endpointDto = await connection.QueryFirstOrDefaultAsync<DynamicApiEndpointDto>(sql, new { Id = id });
            return endpointDto?.ToEntity();
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "获取API端点失败，ID: {EndpointId}", id);
            throw;
        }
    }

    /// <summary>
    /// 根据路径和方法获取API端点
    /// </summary>
    public async Task<DynamicApiEndpoint?> GetByPathAndMethodAsync(string pathTemplate, HttpMethod method, string? tenantId = null, CancellationToken cancellationToken = default)
    {
        var sql = @"
            SELECT * FROM ""DynamicApiEndpoints""
            WHERE ""PathTemplate"" = @PathTemplate
            AND ""Method"" = @Method
            AND ""IsDeleted"" = false";

        object parameters;

        if (!string.IsNullOrWhiteSpace(tenantId))
        {
            sql += " AND TenantId = @TenantId";
            parameters = new { PathTemplate = pathTemplate, Method = (int)method, TenantId = tenantId };
        }
        else
        {
            parameters = new { PathTemplate = pathTemplate, Method = (int)method };
        }

        try
        {
            using var connection = CreateConnection();
            var endpointDto = await connection.QueryFirstOrDefaultAsync<DynamicApiEndpointDto>(sql, parameters);
            return endpointDto?.ToEntity();
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "根据路径和方法获取API端点失败，路径: {PathTemplate}, 方法: {Method}", pathTemplate, method);
            throw;
        }
    }

    /// <summary>
    /// 根据路径和方法获取API端点（字符串方法参数重载）
    /// </summary>
    public async Task<IEnumerable<DynamicApiEndpoint>> GetByPathAndMethodAsync(
        string pathTemplate,
        string method,
        string? tenantId = null,
        CancellationToken cancellationToken = default)
    {
        const string sql = @"
            SELECT * FROM ""DynamicApiEndpoints""
            WHERE ""PathTemplate"" = @PathTemplate
            AND ""Method"" = @Method
            AND ""IsDeleted"" = false
            AND (@TenantId IS NULL OR ""TenantId"" = @TenantId)
            ORDER BY ""Priority"" DESC, ""CreatedAt"" ASC";

        try
        {
            using var connection = CreateConnection();
            var methodEnum = ConvertHttpMethodToEnum(method);

            var endpointDtos = await connection.QueryAsync<DynamicApiEndpointDto>(sql, new
            {
                PathTemplate = pathTemplate,
                Method = (int)methodEnum,
                TenantId = tenantId
            });

            var endpoints = endpointDtos.Select(dto => dto.ToEntity()).ToList();

            _logger.LogDebug("根据路径和方法获取端点完成，路径: {PathTemplate}, 方法: {Method}, 返回 {Count} 条记录",
                pathTemplate, method, endpoints.Count);

            return endpoints;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "根据路径和方法获取端点失败，路径: {PathTemplate}, 方法: {Method}", pathTemplate, method);
            throw;
        }
    }

    /// <summary>
    /// 转换HTTP方法字符串为枚举
    /// </summary>
    private HttpMethod ConvertHttpMethodToEnum(string method)
    {
        return method.ToUpperInvariant() switch
        {
            "GET" => HttpMethod.Get,
            "POST" => HttpMethod.Post,
            "PUT" => HttpMethod.Put,
            "DELETE" => HttpMethod.Delete,
            "PATCH" => HttpMethod.Patch,
            "HEAD" => HttpMethod.Head,
            "OPTIONS" => HttpMethod.Options,
            _ => HttpMethod.Get
        };
    }

    /// <summary>
    /// 获取匹配路径的API端点列表
    /// </summary>
    public async Task<IEnumerable<DynamicApiEndpoint>> GetMatchingEndpointsAsync(string requestPath, HttpMethod method, string? tenantId = null, CancellationToken cancellationToken = default)
    {
        var sql = @"
            SELECT * FROM ""DynamicApiEndpoints""
            WHERE ""Method"" = @Method
            AND ""IsEnabled"" = true
            AND ""IsDeleted"" = false";

        object parameters;

        if (!string.IsNullOrWhiteSpace(tenantId))
        {
            sql += " AND TenantId = @TenantId";
            parameters = new { Method = (int)method, TenantId = tenantId };
        }
        else
        {
            parameters = new { Method = (int)method };
        }

        sql += " ORDER BY \"Priority\" DESC, \"CreatedAt\" ASC";

        try
        {
            using var connection = CreateConnection();
            var endpointDtos = await connection.QueryAsync<DynamicApiEndpointDto>(sql, parameters);
            return endpointDtos.Select(dto => dto.ToEntity());
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "获取匹配API端点失败，路径: {RequestPath}, 方法: {Method}", requestPath, method);
            throw;
        }
    }

    /// <summary>
    /// 获取所有启用的API端点
    /// </summary>
    public async Task<IEnumerable<DynamicApiEndpoint>> GetEnabledEndpointsAsync(string? tenantId = null, CancellationToken cancellationToken = default)
    {
        var sql = @"
            SELECT * FROM ""DynamicApiEndpoints""
            WHERE ""IsEnabled"" = true
            AND ""IsDeleted"" = false";

        if (!string.IsNullOrWhiteSpace(tenantId))
        {
            sql += " AND \"TenantId\" = @TenantId";
        }

        sql += " ORDER BY \"Priority\" DESC, \"Name\" ASC";

        try
        {
            using var connection = CreateConnection();
            var endpointDtos = await connection.QueryAsync<DynamicApiEndpointDto>(sql, new { TenantId = tenantId });
            return endpointDtos.Select(dto => dto.ToEntity());
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "获取启用的API端点失败");
            throw;
        }
    }

    /// <summary>
    /// 检查路径和方法组合是否已存在
    /// </summary>
    public async Task<bool> ExistsByPathAndMethodAsync(string pathTemplate, HttpMethod method, string? tenantId = null, Guid? excludeId = null, CancellationToken cancellationToken = default)
    {
        var sql = "SELECT COUNT(1) FROM \"DynamicApiEndpoints\" WHERE \"PathTemplate\" = @PathTemplate AND \"Method\" = @Method AND \"IsDeleted\" = false";
        var parameters = new DynamicParameters();
        parameters.Add("PathTemplate", pathTemplate);
        parameters.Add("Method", (int)method);

        if (!string.IsNullOrWhiteSpace(tenantId))
        {
            sql += " AND \"TenantId\" = @TenantId";
            parameters.Add("TenantId", tenantId);
        }

        if (excludeId.HasValue)
        {
            sql += " AND \"Id\" != @ExcludeId";
            parameters.Add("ExcludeId", excludeId.Value);
        }

        try
        {
            using var connection = CreateConnection();
            var count = await connection.QuerySingleAsync<int>(sql, parameters);
            return count > 0;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "检查API端点是否存在失败，路径: {PathTemplate}, 方法: {Method}", pathTemplate, method);
            throw;
        }
    }

    /// <summary>
    /// 创建API端点
    /// </summary>
    public override async Task<DynamicApiEndpoint> CreateAsync(DynamicApiEndpoint endpoint, CancellationToken cancellationToken = default)
    {
        const string sql = @"
            INSERT INTO ""DynamicApiEndpoints"" (
                ""Id"", ""Name"", ""Description"", ""Method"", ""PathTemplate"", ""TargetService"", ""TargetUrl"",
                ""IsEnabled"", ""RequireAuthentication"", ""RequiredRoles"", ""RequiredPermissions"",
                ""RateLimitPerMinute"", ""TimeoutSeconds"", ""RetryCount"", ""CacheSeconds"",
                ""Headers"", ""QueryParameters"", ""RequestTransformation"", ""ResponseTransformation"",
                ""Version"", ""Tags"", ""TenantId"", ""Priority"", ""Metadata"", ""LastAccessedAt"", ""AccessCount"",
                ""CreatedAt"", ""UpdatedAt"", ""CreatedBy"", ""UpdatedBy"", ""IsDeleted"", ""DeletedAt"", ""DeletedBy""
            ) VALUES (
                @Id, @Name, @Description, @Method, @PathTemplate, @TargetService, @TargetUrl,
                @IsEnabled, @RequireAuthentication, @RequiredRoles, @RequiredPermissions,
                @RateLimitPerMinute, @TimeoutSeconds, @RetryCount, @CacheSeconds,
                @Headers, @QueryParameters, @RequestTransformation, @ResponseTransformation,
                @Version, @Tags, @TenantId, @Priority, @Metadata, @LastAccessedAt, @AccessCount,
                @CreatedAt, @UpdatedAt, @CreatedBy, @UpdatedBy, @IsDeleted, @DeletedAt, @DeletedBy
            )";

        try
        {
            using var connection = CreateConnection();
            var endpointDto = DynamicApiEndpointDto.FromEntity(endpoint);
            await connection.ExecuteAsync(sql, endpointDto);

            _logger.LogInformation("API端点创建成功，ID: {EndpointId}, 名称: {Name}", endpoint.Id, endpoint.Name);
            return endpoint;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "创建API端点失败，名称: {Name}", endpoint.Name);
            throw;
        }
    }

    /// <summary>
    /// 更新API端点
    /// </summary>
    public override async Task<DynamicApiEndpoint> UpdateAsync(DynamicApiEndpoint endpoint, CancellationToken cancellationToken = default)
    {
        const string sql = @"
            UPDATE ""DynamicApiEndpoints"" SET
                ""Name"" = @Name, ""Description"" = @Description, ""Method"" = @Method, ""PathTemplate"" = @PathTemplate,
                ""TargetService"" = @TargetService, ""TargetUrl"" = @TargetUrl, ""IsEnabled"" = @IsEnabled,
                ""RequireAuthentication"" = @RequireAuthentication, ""RequiredRoles"" = @RequiredRoles,
                ""RequiredPermissions"" = @RequiredPermissions, ""RateLimitPerMinute"" = @RateLimitPerMinute,
                ""TimeoutSeconds"" = @TimeoutSeconds, ""RetryCount"" = @RetryCount, ""CacheSeconds"" = @CacheSeconds,
                ""Headers"" = @Headers, ""QueryParameters"" = @QueryParameters, ""RequestTransformation"" = @RequestTransformation,
                ""ResponseTransformation"" = @ResponseTransformation, ""Version"" = @Version, ""Tags"" = @Tags,
                ""TenantId"" = @TenantId, ""Priority"" = @Priority, ""Metadata"" = @Metadata,
                ""UpdatedAt"" = @UpdatedAt, ""UpdatedBy"" = @UpdatedBy
            WHERE ""Id"" = @Id AND ""IsDeleted"" = false";

        try
        {
            using var connection = CreateConnection();
            var endpointDto = DynamicApiEndpointDto.FromEntity(endpoint);
            var affectedRows = await connection.ExecuteAsync(sql, endpointDto);

            if (affectedRows == 0)
                throw new InvalidOperationException($"API端点不存在或已被删除，ID: {endpoint.Id}");

            _logger.LogInformation("API端点更新成功，ID: {EndpointId}, 名称: {Name}", endpoint.Id, endpoint.Name);
            return endpoint;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "更新API端点失败，ID: {EndpointId}", endpoint.Id);
            throw;
        }
    }

    /// <summary>
    /// 删除API端点（软删除）
    /// </summary>
    public override async Task<bool> DeleteAsync(Guid id, string? deletedBy = null, CancellationToken cancellationToken = default)
    {
        const string sql = @"
            UPDATE ""DynamicApiEndpoints"" SET
                ""IsDeleted"" = true,
                ""DeletedAt"" = @DeletedAt,
                ""DeletedBy"" = @DeletedBy,
                ""UpdatedAt"" = @UpdatedAt,
                ""UpdatedBy"" = @UpdatedBy
            WHERE ""Id"" = @Id AND ""IsDeleted"" = false";

        try
        {
            using var connection = CreateConnection();
            var now = DateTime.UtcNow;
            var affectedRows = await connection.ExecuteAsync(sql, new
            {
                Id = id,
                DeletedAt = now,
                DeletedBy = deletedBy,
                UpdatedAt = now,
                UpdatedBy = deletedBy
            });

            var success = affectedRows > 0;
            if (success)
            {
                _logger.LogInformation("API端点删除成功，ID: {EndpointId}", id);
            }
            else
            {
                _logger.LogWarning("API端点删除失败，端点不存在或已被删除，ID: {EndpointId}", id);
            }

            return success;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "删除API端点失败，ID: {EndpointId}", id);
            throw;
        }
    }

    /// <summary>
    /// 物理删除API端点
    /// </summary>
    public override async Task<bool> HardDeleteAsync(Guid id, CancellationToken cancellationToken = default)
    {
        const string sql = "DELETE FROM \"DynamicApiEndpoints\" WHERE \"Id\" = @Id";

        try
        {
            using var connection = CreateConnection();
            var affectedRows = await connection.ExecuteAsync(sql, new { Id = id });

            var success = affectedRows > 0;
            if (success)
            {
                _logger.LogInformation("API端点物理删除成功，ID: {EndpointId}", id);
            }
            else
            {
                _logger.LogWarning("API端点物理删除失败，端点不存在，ID: {EndpointId}", id);
            }

            return success;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "物理删除API端点失败，ID: {EndpointId}", id);
            throw;
        }
    }

    /// <summary>
    /// 记录API端点访问
    /// </summary>
    public async Task<bool> RecordAccessAsync(Guid id, CancellationToken cancellationToken = default)
    {
        const string sql = @"
            UPDATE ""DynamicApiEndpoints"" SET
                ""LastAccessedAt"" = @LastAccessedAt,
                ""AccessCount"" = ""AccessCount"" + 1,
                ""UpdatedAt"" = @UpdatedAt
            WHERE ""Id"" = @Id AND ""IsDeleted"" = false";

        try
        {
            using var connection = CreateConnection();
            var now = DateTime.UtcNow;
            var affectedRows = await connection.ExecuteAsync(sql, new
            {
                Id = id,
                LastAccessedAt = now,
                UpdatedAt = now
            });

            return affectedRows > 0;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "记录API端点访问失败，ID: {EndpointId}", id);
            throw;
        }
    }

    // 其他方法的实现将在下一个文件中继续...

    #region 未实现的方法（占位符）

    public async Task<(IEnumerable<DynamicApiEndpoint> Endpoints, int TotalCount)> GetPagedAsync(int pageNumber = 1, int pageSize = 20, string? searchTerm = null, string? tenantId = null, bool? isEnabled = null, HttpMethod? method = null, IEnumerable<string>? tags = null, CancellationToken cancellationToken = default)
    {
        try
        {
            var conditions = new List<string> { "\"IsDeleted\" = false" };
            var parameters = new DynamicParameters();

            if (!string.IsNullOrWhiteSpace(tenantId))
            {
                conditions.Add("\"TenantId\" = @TenantId");
                parameters.Add("TenantId", tenantId);
            }

            if (isEnabled.HasValue)
            {
                conditions.Add("\"IsEnabled\" = @IsEnabled");
                parameters.Add("IsEnabled", isEnabled.Value);
            }

            if (method.HasValue)
            {
                conditions.Add("\"Method\" = @Method");
                parameters.Add("Method", (int)method.Value);
            }

            if (!string.IsNullOrWhiteSpace(searchTerm))
            {
                conditions.Add("(\"PathTemplate\" ILIKE @SearchTerm OR \"Name\" ILIKE @SearchTerm OR \"Description\" ILIKE @SearchTerm)");
                parameters.Add("SearchTerm", $"%{searchTerm}%");
            }

            var whereClause = string.Join(" AND ", conditions);
            var offset = (pageNumber - 1) * pageSize;

            var countSql = $"SELECT COUNT(*) FROM \"DynamicApiEndpoints\" WHERE {whereClause}";
            var dataSql = $@"
                SELECT * FROM ""DynamicApiEndpoints""
                WHERE {whereClause}
                ORDER BY ""Priority"" DESC, ""CreatedAt"" DESC
                LIMIT @PageSize OFFSET @Offset";

            parameters.Add("PageSize", pageSize);
            parameters.Add("Offset", offset);

            using var connection = CreateConnection();

            var totalCount = await connection.QuerySingleAsync<int>(countSql, parameters);
            var endpointDtos = await connection.QueryAsync<DynamicApiEndpointDto>(dataSql, parameters);
            var endpoints = endpointDtos.Select(dto => dto.ToEntity());

            return (endpoints, totalCount);
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "获取分页API端点失败");
            throw;
        }
    }

    public async Task<IEnumerable<DynamicApiEndpoint>> GetByTargetServiceAsync(string targetService, string? tenantId = null, CancellationToken cancellationToken = default)
    {
        try
        {
            var sql = @"
                SELECT * FROM ""DynamicApiEndpoints""
                WHERE ""TargetService"" = @TargetService
                AND ""IsDeleted"" = false
                AND ""IsEnabled"" = true";

            var parameters = new DynamicParameters();
            parameters.Add("TargetService", targetService);

            if (!string.IsNullOrWhiteSpace(tenantId))
            {
                sql += " AND \"TenantId\" = @TenantId";
                parameters.Add("TenantId", tenantId);
            }

            sql += " ORDER BY \"Priority\" DESC, \"CreatedAt\" ASC";

            using var connection = CreateConnection();
            var endpointDtos = await connection.QueryAsync<DynamicApiEndpointDto>(sql, parameters);
            return endpointDtos.Select(dto => dto.ToEntity());
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "根据目标服务获取API端点失败: {TargetService}", targetService);
            throw;
        }
    }

    public async Task<IEnumerable<DynamicApiEndpoint>> GetByTagAsync(string tag, string? tenantId = null, CancellationToken cancellationToken = default)
    {
        try
        {
            var sql = @"
                SELECT * FROM ""DynamicApiEndpoints""
                WHERE ""Tags"" @> @Tag::jsonb
                AND ""IsDeleted"" = false
                AND ""IsEnabled"" = true";

            var parameters = new DynamicParameters();
            parameters.Add("Tag", $"[\"{tag}\"]");

            if (!string.IsNullOrWhiteSpace(tenantId))
            {
                sql += " AND \"TenantId\" = @TenantId";
                parameters.Add("TenantId", tenantId);
            }

            sql += " ORDER BY \"Priority\" DESC, \"CreatedAt\" ASC";

            using var connection = CreateConnection();
            var endpointDtos = await connection.QueryAsync<DynamicApiEndpointDto>(sql, parameters);
            return endpointDtos.Select(dto => dto.ToEntity());
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "根据标签获取API端点失败: {Tag}", tag);
            throw;
        }
    }

    public override async Task<IEnumerable<DynamicApiEndpoint>> CreateBatchAsync(IEnumerable<DynamicApiEndpoint> endpoints, CancellationToken cancellationToken = default)
    {
        // 使用基类的批量创建方法
        return await base.CreateBatchAsync(endpoints, cancellationToken);
    }

    public override async Task<IEnumerable<DynamicApiEndpoint>> UpdateBatchAsync(IEnumerable<DynamicApiEndpoint> endpoints, CancellationToken cancellationToken = default)
    {
        // 使用基类的批量更新方法
        return await base.UpdateBatchAsync(endpoints, cancellationToken);
    }

    public async Task<ApiEndpointStatistics> GetStatisticsAsync(string? tenantId = null, CancellationToken cancellationToken = default)
    {
        try
        {
            var tenantCondition = string.IsNullOrWhiteSpace(tenantId) ? "" : "AND \"TenantId\" = @TenantId";
            var parameters = new DynamicParameters();
            if (!string.IsNullOrWhiteSpace(tenantId))
            {
                parameters.Add("TenantId", tenantId);
            }

            var sql = $@"
                SELECT
                    COUNT(*) as TotalEndpoints,
                    COUNT(*) FILTER (WHERE ""IsEnabled"" = true) as EnabledEndpoints,
                    COUNT(*) FILTER (WHERE ""IsEnabled"" = false) as DisabledEndpoints,
                    COUNT(*) FILTER (WHERE ""CreatedAt""::date = CURRENT_DATE) as TodayNewEndpoints,
                    COUNT(*) FILTER (WHERE ""CreatedAt"" >= date_trunc('week', CURRENT_DATE)) as WeekNewEndpoints,
                    COUNT(*) FILTER (WHERE ""CreatedAt"" >= date_trunc('month', CURRENT_DATE)) as MonthNewEndpoints,
                    COALESCE(SUM(""AccessCount""), 0) as TotalAccesses,
                    COALESCE(SUM(CASE WHEN ""LastAccessedAt""::date = CURRENT_DATE THEN ""AccessCount"" ELSE 0 END), 0) as TodayAccesses
                FROM ""DynamicApiEndpoints""
                WHERE ""IsDeleted"" = false {tenantCondition}";

            using var connection = CreateConnection();
            var stats = await connection.QuerySingleAsync<ApiEndpointStatistics>(sql, parameters);

            // 获取按方法分组的统计
            var methodSql = $@"
                SELECT ""Method"", COUNT(*) as Count
                FROM ""DynamicApiEndpoints""
                WHERE ""IsDeleted"" = false {tenantCondition}
                GROUP BY ""Method""";

            var methodStats = await connection.QueryAsync<(int Method, int Count)>(methodSql, parameters);
            stats.EndpointsByMethod = methodStats.ToDictionary(
                x => (HttpMethod)x.Method,
                x => x.Count
            );

            // 获取按服务分组的统计
            var serviceSql = $@"
                SELECT ""TargetService"", COUNT(*) as Count
                FROM ""DynamicApiEndpoints""
                WHERE ""IsDeleted"" = false AND ""TargetService"" IS NOT NULL {tenantCondition}
                GROUP BY ""TargetService""";

            var serviceStats = await connection.QueryAsync<(string Service, int Count)>(serviceSql, parameters);
            stats.EndpointsByService = serviceStats.ToDictionary(
                x => x.Service,
                x => x.Count
            );

            return stats;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "获取API端点统计信息失败");
            throw;
        }
    }

    public async Task<IEnumerable<DynamicApiEndpoint>> GetMostPopularAsync(int count = 10, string? tenantId = null, CancellationToken cancellationToken = default)
    {
        try
        {
            var sql = @"
                SELECT * FROM ""DynamicApiEndpoints""
                WHERE ""IsDeleted"" = false
                AND ""IsEnabled"" = true";

            var parameters = new DynamicParameters();
            if (!string.IsNullOrWhiteSpace(tenantId))
            {
                sql += " AND \"TenantId\" = @TenantId";
                parameters.Add("TenantId", tenantId);
            }

            sql += @"
                ORDER BY ""AccessCount"" DESC, ""LastAccessedAt"" DESC
                LIMIT @Count";

            parameters.Add("Count", count);

            using var connection = CreateConnection();
            var endpointDtos = await connection.QueryAsync<DynamicApiEndpointDto>(sql, parameters);
            return endpointDtos.Select(dto => dto.ToEntity());
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "获取最热门API端点失败");
            throw;
        }
    }

    public async Task<IEnumerable<DynamicApiEndpoint>> GetRecentlyAccessedAsync(int count = 10, string? tenantId = null, CancellationToken cancellationToken = default)
    {
        try
        {
            var sql = @"
                SELECT * FROM ""DynamicApiEndpoints""
                WHERE ""IsDeleted"" = false
                AND ""IsEnabled"" = true
                AND ""LastAccessedAt"" IS NOT NULL";

            var parameters = new DynamicParameters();
            if (!string.IsNullOrWhiteSpace(tenantId))
            {
                sql += " AND \"TenantId\" = @TenantId";
                parameters.Add("TenantId", tenantId);
            }

            sql += @"
                ORDER BY ""LastAccessedAt"" DESC
                LIMIT @Count";

            parameters.Add("Count", count);

            using var connection = CreateConnection();
            var endpointDtos = await connection.QueryAsync<DynamicApiEndpointDto>(sql, parameters);
            return endpointDtos.Select(dto => dto.ToEntity());
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "获取最近访问API端点失败");
            throw;
        }
    }

    public async Task<IEnumerable<string>> GetAllTagsAsync(string? tenantId = null, CancellationToken cancellationToken = default)
    {
        try
        {
            var sql = @"
                SELECT DISTINCT jsonb_array_elements_text(""Tags"") as tag
                FROM ""DynamicApiEndpoints""
                WHERE ""IsDeleted"" = false
                AND ""Tags"" IS NOT NULL";

            var parameters = new DynamicParameters();
            if (!string.IsNullOrWhiteSpace(tenantId))
            {
                sql += " AND \"TenantId\" = @TenantId";
                parameters.Add("TenantId", tenantId);
            }

            sql += " ORDER BY tag";

            using var connection = CreateConnection();
            return await connection.QueryAsync<string>(sql, parameters);
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "获取所有标签失败");
            throw;
        }
    }

    public async Task<IEnumerable<string>> GetAllTargetServicesAsync(string? tenantId = null, CancellationToken cancellationToken = default)
    {
        try
        {
            var sql = @"
                SELECT DISTINCT ""TargetService""
                FROM ""DynamicApiEndpoints""
                WHERE ""IsDeleted"" = false
                AND ""TargetService"" IS NOT NULL";

            var parameters = new DynamicParameters();
            if (!string.IsNullOrWhiteSpace(tenantId))
            {
                sql += " AND \"TenantId\" = @TenantId";
                parameters.Add("TenantId", tenantId);
            }

            sql += " ORDER BY \"TargetService\"";

            using var connection = CreateConnection();
            return await connection.QueryAsync<string>(sql, parameters);
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "获取所有目标服务失败");
            throw;
        }
    }

    public async Task<IEnumerable<DynamicApiEndpoint>> GetAllAsync(string? tenantId = null, CancellationToken cancellationToken = default)
    {
        try
        {
            var sql = @"
                SELECT * FROM ""DynamicApiEndpoints""
                WHERE ""IsDeleted"" = false";

            var parameters = new DynamicParameters();
            if (!string.IsNullOrWhiteSpace(tenantId))
            {
                sql += " AND \"TenantId\" = @TenantId";
                parameters.Add("TenantId", tenantId);
            }

            sql += " ORDER BY \"Priority\" DESC, \"CreatedAt\" ASC";

            using var connection = CreateConnection();
            var endpointDtos = await connection.QueryAsync<DynamicApiEndpointDto>(sql, parameters);
            return endpointDtos.Select(dto => dto.ToEntity());
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "获取所有API端点失败");
            throw;
        }
    }

    #endregion
}
